package com.xinyirun.scm.core.system.mapper.business.adjust;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xinyirun.scm.bean.entity.business.adjust.BAdjustDetailEntity;
import com.xinyirun.scm.bean.system.vo.business.adjust.BAdjustDetailVo;
import com.xinyirun.scm.bean.system.vo.business.adjust.BAdjustVo;
import com.xinyirun.scm.bean.system.vo.business.wms.inventory.*;
import com.xinyirun.scm.common.constant.DictConstant;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * <p>
 * 库存调整 Mapper 接口
 * </p>
 *
 * @author wwl
 * @since 2021-12-09
 */
@Repository
public interface BAdjustDetailMapper extends BaseMapper<BAdjustDetailEntity> {

    String common_select = "                                                                            "
            +  "   SELECT                                                "
            +  "   	t1.*,                                                "
            +  "    t2.name warehouse_name,                              "
            +  "   	t6.spec,                                             "
            +  "   	t6.pm,                                               "
            +  "   	t7.NAME AS goods_name                                "
            +  "   FROM                                                  "
            +  "   	b_adjust_detail t1                                   "
            +  "   	LEFT JOIN m_goods_spec t6 ON t6.id = t1.sku_id       "
            +  "   	LEFT JOIN m_goods t7 ON t7.id = t6.goods_id 	     "
            +  "    LEFT JOIN m_warehouse t2 ON t1.warehouse_id = t2.id  "
            ;

    /**
     * 查看页面查询列表
     */
    @Select("    "
            + common_select
            + "  where true                                                                                       "
            + "    and (t1.adjust_id = #{p1.adjust_id,jdbcType=INTEGER} )                                               "
            + "      ")
    List<BAdjustDetailVo> getAdjustDetailList(@Param("p1") BAdjustVo searchCondition);

    /**
     * 删除状态为制单和驳回的明细数据
     */
    @Select("    "
            + "  delete from b_adjust_detail                                                                                           "
            + "  where adjust_id = #{p1,jdbcType=INTEGER}                                                                              "
            + "  and status in ( '" + DictConstant.DICT_B_ADJUST_STATUS_SAVED+"','" +DictConstant.DICT_B_ADJUST_STATUS_RETURN +"' )    ")
    void statusDelete(@Param("p1") int adjust_id);

    /**
     * 查询同一个货主、仓库、库区、库位、商品 的调整单
     * @param vo
     * @return
     */
    @Select("    "
            + "  select *                                                                                                                 "
            + "    from b_adjust_detail t1                                                                                                "
            + "   where true                                                                                                              "
            + "  and status in ( '" + DictConstant.DICT_B_ADJUST_STATUS_SAVED+"','" +DictConstant.DICT_B_ADJUST_STATUS_SUBMITTED +"' )    "
            + "  and t1.sku_id = #{p1.sku_id,jdbcType=INTEGER}                                                                            "
            + "  and t1.warehouse_id = #{p1.warehouse_id,jdbcType=INTEGER}                                                                "
            + "  and t1.location_id = #{p1.location_id,jdbcType=INTEGER}                                                                  "
            + "  and t1.bin_id = #{p1.bin_id,jdbcType=INTEGER}                                                                            "
            + " ")
    List<BAdjustDetailVo> getWaitAuditDatas(@Param("p1") BAdjustDetailVo vo);

    /**
     * 悲观锁
     * @param id
     * @return
     */
    @Select("    "
            + "  select *                        "
            + "    from b_adjust_detail t1       "
            + "   where t1.id = #{p1}            "
            + "     for update                   "
            + "                                  ")
    BAdjustDetailEntity setBillAdjustForUpdate(@Param("p1") Integer id);

    @Select(
            "<script>"
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + "		SELECT                                                                                             "
            + "			t1.warehouse_type,                                                                             "
            + "			t3.`name` goods_name,                                                                          "
            + "			t1.`name` warehouse_name,                                                                      "
            + "			round(SUM(qty_diff), 4)  qty,                                                                          "
            + "         t4.label as warehouse_type_name   ,                                                            "
            + "         t1.id warehouse_id,                                                                            "
            + "         concat(t1.id, '_', t3.id) id,                                                                  "
            + "         t3.code goods_code                                                                             "
            + "		FROM                                                                                               "
            + "			b_adjust_detail t                                                                              "
            + "		LEFT JOIN m_warehouse t1 ON t.warehouse_id = t1.id                                                 "
            + "		LEFT JOIN m_goods_spec t2 ON t.sku_id = t2.id                                                      "
            + "		LEFT JOIN m_goods t3 on t2.goods_id = t3.id                                                        "
            + "       LEFT JOIN v_dict_info t4 ON t4.code = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE +"' and t1.warehouse_type = t4.dict_value"
            + "	    WHERE t.`status` = " + DictConstant.DICT_B_ADJUST_STATUS_PASSED

            + "<if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                                   "
            + "     and t1.id in"
            + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>"
            + "         #{item}                                                                                        "
            + "     </foreach>                                                                                         "
            + "</if>                                                                                                   "
            + " and (t1.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '')                          "
            + " and (t3.name like concat('%', #{p1.goods_name}, '%') or #{p1.goods_name} is null or #{p1.goods_name} = '')"
            + " and (t3.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')"
            + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')       "
            + " ${p1.params.dataScopeAnnotation}                                                                       "
            + "		GROUP BY t.warehouse_id, t3.id                                                                     "
            + "</script>                                                                                               "
    )
    IPage<BWarehouseGoodsVo> queryAdjustInventory(Page<BAdjustDetailEntity> pageCondition, @Param("p1") BWarehouseGoodsVo searchCondition);

    @Select("<script>                                                                                                   "
            + " ${p1.params.dataScopeAnnotation_with}                                                                   "
            + " select                                                                                                  "
            + "   tab1.goods_id,                                                                                        "
            + "   tab1.warehouse_id,                                                                                    "
            + "   tab1.goods_code,                                                                                      "
            + "   tab1.in_qty,                                                                                          "
            + "   tab1.out_qty,                                                                                         "
            + "   tab1.qty_diff,                                                                                        "
            + "   tab1.qty,                                                                                             "
            + "   tab1.goods_name,                                                                                      "
            + "   tab1.warehouse_name,                                                                                  "
            + "   tab1.warehouse_type_name,                                                                             "
            + "   tab1.warehouse_type,                                                                                  "
            + "   tab1.type_id,                                                                                         "
            + "   tab1.id,                                                                                               "
            + "   tab1.return_qty                                                                                               "
            + " from (                                                                                                  "
            + "	SELECT                                                                                                  "
            + "		tt2.goods_id ,                                                                                      "
            + "		tt2.warehouse_id ,                                                                                  "
            + "     tt6.code goods_code,                                                                                "
            + "		round(ifnull(sum(tt3.in_qty), 0), 4) in_qty,                                                        "
            + "		round(ifnull(sum(tt4.out_qty), 0), 4) out_qty,                                                      "
            + "		round(ifnull(sum(tt5.qty_diff), 0), 4) qty_diff,                                                    "
            + "		round(ifnull(sum(tt9.qty_avaible), 0), 4) qty,                                                      "
            + "		tt6.`name` goods_name,                                                                              "
            + "		tt7.`name` warehouse_name,                                                                          "
            + "     tt8.label as warehouse_type_name,                                                                   "
            + "     tt7.warehouse_type,                                                                                 "
            + "     concat(tt7.warehouse_type, '_', tt2.goods_id)  type_id,                                             "
            + "     concat(tt7.id, '_', tt2.goods_id)  id,                                                              "
            + "     round(ifnull(sum(tt10.return_qty), 0), 4) return_qty                                                "
            + "	FROM                                                                                                    "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		FROM                                                                                                "
            + "			(                                                                                               "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_in t                                                                                      "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '"+ DictConstant.DICT_B_IN_STATUS_TWO +"'                                         "
            // 排除仓库类型是2时的生产入库类型的入库单
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_out t                                                                                     "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '"+ DictConstant.DICT_B_OUT_STATUS_PASSED +"'                                        "
            // 排除当 仓库类型是2时的领料出库类型的出库单
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_adjust_detail t                                                                           "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "         UNION ALL                                                                                       "
            + "      SELECT t.warehouse_id, t1.goods_id FROM m_inventory t LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id"
            + "			) tt1                                                                                           "
            + "     WHERE true                                                                                          "
            + " ${p1.params.dataScopeAnnotation}                                                                        "
            + "		GROUP BY                                                                                            "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		) tt2                                                                                               "
            + "		LEFT JOIN (                                                                                         "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight ) in_qty                                                                     "
            + "		FROM                                                                                                "
            + "			b_in t                                                                                          "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
            // 排除仓库类型是2时的生产入库类型的入库单
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt3 ON tt2.warehouse_id = tt3.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt3.goods_id                                                                     "
            + "		left join                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight ) - sum(ifnull(return_qty,0)) out_qty                                        "
            + "		FROM                                                                                                "
            + "			b_out t                                                                                         "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED +"'                                       "
            // 排除当 仓库类型是2时的领料出库类型的出库单
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt4 on tt2.warehouse_id = tt4.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt4.goods_id                                                                     "
            + "		left JOIN                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( qty_diff ) qty_diff                                                                        "
            + "		FROM                                                                                                "
            + "			b_adjust_detail t                                                                               "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "		WHERE                                                                                               "
            + "			t.STATUS = " + DictConstant.DICT_B_ADJUST_STATUS_PASSED
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt5 on tt2.warehouse_id = tt5.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt5.goods_id                                                                     "
            + "     LEFT JOIN (                                                                                         "
            +  "      SELECT                                                                                            "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id,                                                                                    "
            +  "      	SUM( t.qty_avaible ) qty_avaible                                                                "
            +  "      FROM                                                                                              "
            +  "      	`m_inventory` t                                                                                 "
            +  "      	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "      GROUP BY                                                                                          "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id                                                                                     "
            + "     ) tt9 ON tt9.warehouse_id = tt2.warehouse_id AND tt9.goods_id = tt2.goods_id                        "

            + "     LEFT JOIN (                                                                                         "
            +  "     SELECT                                                                                             "
            +  "     	sum( t1.actual_count ) AS return_qty,                                                         "
            +  "     	t1.warehouse_id,                                                                                "
            +  "     	t3.goods_id                                                                                     "
            +  "     FROM                                                                                               "
            +  "     	b_in t1                                                                                         "
            +  "     	LEFT JOIN b_in_extra t2 ON t1.id = t2.in_id                                                     "
            +  "     	LEFT JOIN m_goods_spec t3 ON t1.sku_id = t3.id                                                  "
            +  "     WHERE TRUE                                                                                         "
            +  "     	AND t1.`status` = '"+DictConstant.DICT_B_IN_STATUS_TWO+"'                                     "
//            +  "     	AND t1.type = '"+DictConstant.DICT_B_IN_TYPE_JG_TH+"'                                            "
            +  "     	GROUP BY t1.warehouse_id,t3.goods_id                                            "
            + "     ) tt10 ON tt10.warehouse_id = tt2.warehouse_id AND tt10.goods_id = tt2.goods_id                      "

            + "		left join m_goods tt6 on tt2.goods_id = tt6.id                                                      "
            + "		left join m_warehouse tt7 on tt2.warehouse_id = tt7.id                                              "
            + "     LEFT JOIN v_dict_info tt8 ON tt8.code = '" + DictConstant.DICT_M_WAREHOUSE_TYPE +"' and tt7.warehouse_type = tt8.dict_value"
            + "     where (tt7.warehouse_type = #{p1.warehouse_type}  or #{p1.warehouse_type,jdbcType=VARCHAR} is null or #{p1.warehouse_type,jdbcType=VARCHAR} = '')"
            + "<if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                                    "
            + "     and tt2.warehouse_id in                                                                             "
            + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>  "
            + "         #{item}                                                                                         "
            + "     </foreach>                                                                                          "
            + "</if>                                                                                                    "
            + "     and (tt7.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '')                      "
            + "     and (tt6.name like concat('%', #{p1.goods_name}, '%') or #{p1.goods_name} is null or #{p1.goods_name} = '')"
            + "     and (tt6.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')"
            + "<if test='p1.query_type == 1'>                                                                           "
            + " group by   tt7.warehouse_type, tt2.goods_id                                                             "
            + "</if>                                                                                                    "
            + "<if test='p1.query_type == 2'>                                                                           "
            + " group by   tt2.warehouse_id, tt2.goods_id                                                               "
            + "</if>                                                                                                    "
            + "  ) tab1                                                                                                 "
            + " where round(tab1.in_qty, 4) != 0                                                                         "
            + " or round(tab1.out_qty, 4) != 0                                                                           "
            + " or round(tab1.qty_diff, 4) != 0                                                                          "
            + " or round(tab1.qty, 4) != 0                                                                               "
            + "</script>                                                                                                "
    )
    IPage<BWarehouseGoodsVo> queryInventoryList(Page<BAdjustDetailEntity> pageCondition, @Param("p1") BWarehouseGoodsVo searchCondition);

    @Select("<script>                                                                                                   "
            + " ${p1.params.dataScopeAnnotation_with}                                                                   "
            + "	SELECT                                                                                                  "
            + "		ifnull(sum(tt3.in_qty), 0) in_qty,                                                                  "
            + "		ifnull(sum(tt4.out_qty), 0) out_qty,                                                                "
            + "		ifnull(sum(tt5.qty_diff), 0) qty_diff,                                                              "
            + "		ifnull(sum(tt9.qty_avaible), 0) qty,                                                                "
            + "		ifnull(sum(tt10.return_qty), 0) return_sum_qty                                                      "
            + "	FROM                                                                                                    "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		FROM                                                                                                "
            + "			(                                                                                               "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_in t                                                                                      "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_out t                                                                                     "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_adjust_detail t                                                                           "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "         UNION ALL                                                                                       "
            + "      SELECT t.warehouse_id, t1.goods_id FROM m_inventory t LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id"
            + "			) tt1                                                                                           "
            + "     WHERE true                                                                                          "
            + "${p1.params.dataScopeAnnotation}                                                                         "
            + "		GROUP BY                                                                                            "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		) tt2                                                                                               "
            + "		LEFT JOIN (                                                                                         "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight ) in_qty                                                                     "
            + "		FROM                                                                                                "
            + "			b_in t                                                                                          "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
            // 排除仓库类型是2时的生产入库类型的入库单
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt3 ON tt2.warehouse_id = tt3.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt3.goods_id                                                                     "
            + "		left join                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight )  - sum(ifnull(return_qty,0)) out_qty                                       "
            + "		FROM                                                                                                "
            + "			b_out t                                                                                         "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
            // 排除当 仓库类型是2时的领料出库类型的出库单
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt4 on tt2.warehouse_id = tt4.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt4.goods_id                                                                     "
            + "		left JOIN                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( qty_diff ) qty_diff                                                                        "
            + "		FROM                                                                                                "
            + "			b_adjust_detail t                                                                               "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt5 on tt2.warehouse_id = tt5.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt5.goods_id                                                                     "
            + "     LEFT JOIN (                                                                                         "
            +  "      SELECT                                                                                            "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id,                                                                                    "
            +  "      	SUM( t.qty_avaible ) qty_avaible                                                                "
            +  "      FROM                                                                                              "
            +  "      	`m_inventory` t                                                                                 "
            +  "      	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "      GROUP BY                                                                                          "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id                                                                                     "
            + "     ) tt9 ON tt9.warehouse_id = tt2.warehouse_id AND tt9.goods_id = tt2.goods_id                        "

            + "     LEFT JOIN (                                                                                         "
            +  "     SELECT                                                                                             "
            +  "     	sum( t1.actual_count ) AS return_qty,                                                         "
            +  "     	t1.warehouse_id,                                                                                "
            +  "     	t3.goods_id                                                                                     "
            +  "     FROM                                                                                               "
            +  "     	b_in t1                                                                                         "
            +  "     	LEFT JOIN b_in_extra t2 ON t1.id = t2.in_id                                                     "
            +  "     	LEFT JOIN m_goods_spec t3 ON t1.sku_id = t3.id                                                  "
            +  "     WHERE TRUE                                                                                         "
            +  "     	AND t1.`status` = '"+DictConstant.DICT_B_IN_STATUS_TWO+"'                                     "
//            +  "     	AND t1.type = '"+DictConstant.DICT_B_IN_TYPE_JG_TH+"'                                            "
            +  "     	GROUP BY t1.warehouse_id,t3.goods_id                                            "
            + "     ) tt10 ON tt10.warehouse_id = tt2.warehouse_id AND tt10.goods_id = tt2.goods_id                      "

            + "		left join m_goods tt6 on tt2.goods_id = tt6.id                                                      "
            + "		left join m_warehouse tt7 on tt2.warehouse_id = tt7.id                                              "
            + "     LEFT JOIN v_dict_info tt8 ON tt8.code = '" + DictConstant.DICT_M_WAREHOUSE_TYPE +"' and tt7.warehouse_type = tt8.dict_value"
            + "     where (tt7.warehouse_type = #{p1.warehouse_type}  or #{p1.warehouse_type,jdbcType=VARCHAR} is null or #{p1.warehouse_type,jdbcType=VARCHAR} = '')"
            + "<if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                                    "
            + "     and tt2.warehouse_id in                                                                             "
            + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>  "
            + "         #{item}                                                                                         "
            + "     </foreach>                                                                                          "
            + "</if>                                                                                                    "
            + "     and (tt7.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '')                      "
            + "     and (tt6.name like concat('%', #{p1.goods_name}, '%') or #{p1.goods_name} is null or #{p1.goods_name} = '')"
            + "     and (tt6.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')"
            + "</script>                                                                                                "
    )
    BWarehouseGoodsVo queryReportInventorySum(@Param("p1") BWarehouseGoodsVo searchCondition);

    @Select("<script>                                                                                                   "
            + "	SELECT                                                                                                  "
            + "     @row_num:= @row_num+ 1 as no,                                                                       "
            + "		ifnull(sum(tt3.in_qty), 0) in_qty,                                                                  "
            + "		ifnull(sum(tt4.out_qty), 0) out_qty,                                                                "
            + "		ifnull(sum(tt5.qty_diff), 0) qty_diff,                                                              "
            + "		ifnull(sum(tt9.qty_avaible), 0) qty,                                                        "
            + "		tt6.`name` goods_name,                                                                              "
            + "		tt6.`code` goods_code,                                                                              "
            + "		tt8.label as warehouse_type_name,                                                                   "
            + "		ifnull(sum(tt11.return_qty), 0) return_qty                                                          "
            + "	FROM                                                                                                    "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		FROM                                                                                                "
            + "			(                                                                                               "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_in t                                                                                      "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
            // 排除仓库类型是2时的生产入库类型的入库单
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_out t                                                                                     "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
            // 排除当 仓库类型是2时的领料出库类型的出库单
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_adjust_detail t                                                                           "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + "         UNION ALL                                                                                       "
            + "      SELECT t.warehouse_id, t1.goods_id FROM m_inventory t LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id"
            + "			) tt1                                                                                           "
            + "		GROUP BY                                                                                            "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		) tt2                                                                                               "
            + "		LEFT JOIN (                                                                                         "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight ) in_qty                                                                     "
            + "		FROM                                                                                                "
            + "			b_in t                                                                                          "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
            // 排除仓库类型是2时的生产入库类型的入库单
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt3 ON tt2.warehouse_id = tt3.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt3.goods_id                                                                     "
            + "		left join                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight )  - sum(ifnull(return_qty,0)) out_qty                                       "
            + "		FROM                                                                                                "
            + "			b_out t                                                                                         "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
            // 排除当 仓库类型是2时的领料出库类型的出库单
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt4 on tt2.warehouse_id = tt4.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt4.goods_id                                                                     "
            + "		left JOIN                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( qty_diff ) qty_diff                                                                        "
            + "		FROM                                                                                                "
            + "			b_adjust_detail t                                                                               "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt5 on tt2.warehouse_id = tt5.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt5.goods_id                                                                     "
            + "     LEFT JOIN (                                                                                         "
            +  "      SELECT                                                                                            "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id,                                                                                    "
            +  "      	SUM( t.qty_avaible ) qty_avaible                                                                "
            +  "      FROM                                                                                              "
            +  "      	`m_inventory` t                                                                                 "
            +  "      	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "      GROUP BY                                                                                          "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id                                                                                     "
            + "     ) tt9 ON tt9.warehouse_id = tt2.warehouse_id AND tt9.goods_id = tt2.goods_id                        "

            + "     LEFT JOIN (                                                                                         "
            +  "     SELECT                                                                                             "
            +  "     	sum( t1.actual_count ) AS return_qty,                                                           "
            +  "     	t1.warehouse_id,                                                                                "
            +  "     	t3.goods_id                                                                                     "
            +  "     FROM                                                                                               "
            +  "     	b_in t1                                                                                         "
            +  "     	LEFT JOIN b_in_extra t2 ON t1.id = t2.in_id                                                     "
            +  "     	LEFT JOIN m_goods_spec t3 ON t1.sku_id = t3.id                                                  "
            +  "     WHERE TRUE                                                                                         "
//            +  "     	AND t1.`status` = '"+DictConstant.DICT_B_IN_STATUS_TWO+"'                                    "
//            +  "     	AND t1.type = '"+DictConstant.DICT_B_IN_TYPE_JG_TH+"'                                           "
            +  "     	GROUP BY t1.warehouse_id,t3.goods_id                                                            "
            + "     ) tt11 ON tt11.warehouse_id = tt2.warehouse_id AND tt11.goods_id = tt2.goods_id                     "

            + "		left join m_goods tt6 on tt2.goods_id = tt6.id                                                      "
            + "		left join m_warehouse tt7 on tt2.warehouse_id = tt7.id                                              "
            + "     LEFT JOIN v_dict_info tt8 ON tt8.code = '" + DictConstant.DICT_M_WAREHOUSE_TYPE +"' and tt7.warehouse_type = tt8.dict_value"
            + "   ,(select @row_num:=0) tt10                                                                             "
            + "<if test='p1 != null and p1.size != 0'>                                                                  "
            + "    where concat(tt7.warehouse_type, '_', tt2.goods_id) in                                               "
            + "    <foreach collection='p1' item='item' index='index' open='(' separator=',' close=')'>                 "
            + "       #{item.type_id}                                                                                   "
            + "    </foreach>                                                                                           "
            + "</if>                                                                                                    "
            + "  group by   tt7.warehouse_type, tt2.goods_id                                                            "
            + "</script>                                                                                                "
    )
    List<BWarehouseGoodsExportVo> queryReportInventoryExport(@Param("p1") List<BWarehouseGoodsVo> searchCondition);

    @Select("<script>                                                                                                   "
            + " ${p1.params.dataScopeAnnotation_with}                                                                   "
            + " select                                                                                                  "
            + "  tab.in_qty,                                                                                            "
            + "  tab.out_qty,                                                                                           "
            + "  tab.qty_diff,                                                                                          "
            + "  tab.qty_avaible qty,                                                                                   "
            + "  tab.goods_name,                                                                                        "
            + "  tab.goods_code,                                                                                        "
            + "  tab.warehouse_type_name,                                                                               "
            + "  tab.return_qty,                                                                               "
            + "      @row_num:= @row_num+ 1 as no                                                                       "
            + " from (                                                                                                  "
            + "	SELECT                                                                                                  "
            + "		ifnull(sum(tt3.in_qty), 0) in_qty,                                                                  "
            + "		ifnull(sum(tt4.out_qty), 0) out_qty,                                                                "
            + "		ifnull(sum(tt5.qty_diff), 0) qty_diff,                                                              "
            + "		ifnull(sum(tt9.qty_avaible), 0) qty_avaible,                                                        "
            + "		tt6.`name` goods_name,                                                                              "
            + "		tt6.`code` goods_code,                                                                              "
            + "     tt8.label as warehouse_type_name,                                                                    "
            + "     ifnull(sum(tt10.return_qty), 0) as return_qty                                                       "
            + "	FROM                                                                                                    "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		FROM                                                                                                "
            + "			(                                                                                               "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_in t                                                                                      "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
            // 排除仓库类型是2时的生产入库类型的入库单
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_out t                                                                                     "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
            // 排除当 仓库类型是2时的领料出库类型的出库单
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_adjust_detail t                                                                           "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "         UNION ALL                                                                                       "
            + "      SELECT t.warehouse_id, t1.goods_id FROM m_inventory t LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id"
            + "			) tt1                                                                                           "
            + "     WHERE true                                                                                          "
            + "${p1.params.dataScopeAnnotation}                                                                         "
            + "		GROUP BY                                                                                            "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		) tt2                                                                                               "
            + "		LEFT JOIN (                                                                                         "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight ) in_qty                                                                     "
            + "		FROM                                                                                                "
            + "			b_in t                                                                                          "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
            // 排除仓库类型是2时的生产入库类型的入库单
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt3 ON tt2.warehouse_id = tt3.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt3.goods_id                                                                     "
            + "		left join                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight )  - sum(ifnull(return_qty,0)) out_qty                                       "
            + "		FROM                                                                                                "
            + "			b_out t                                                                                         "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
            // 排除当 仓库类型是2时的领料出库类型的出库单
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt4 on tt2.warehouse_id = tt4.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt4.goods_id                                                                     "
            + "		left JOIN                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( qty_diff ) qty_diff                                                                        "
            + "		FROM                                                                                                "
            + "			b_adjust_detail t                                                                               "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt5 on tt2.warehouse_id = tt5.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt5.goods_id                                                                     "
            + "     LEFT JOIN (                                                                                         "
            +  "      SELECT                                                                                            "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id,                                                                                    "
            +  "      	SUM( t.qty_avaible ) qty_avaible                                                                "
            +  "      FROM                                                                                              "
            +  "      	`m_inventory` t                                                                                 "
            +  "      	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "      GROUP BY                                                                                          "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id                                                                                     "
            + "     ) tt9 ON tt9.warehouse_id = tt2.warehouse_id AND tt9.goods_id = tt2.goods_id                        "

            + "     LEFT JOIN (                                                                                         "
            +  "     SELECT                                                                                             "
            +  "     	sum( t1.actual_count ) AS return_qty,                                                           "
            +  "     	t1.warehouse_id,                                                                                "
            +  "     	t3.goods_id                                                                                     "
            +  "     FROM                                                                                               "
            +  "     	b_in t1                                                                                         "
            +  "     	LEFT JOIN b_in_extra t2 ON t1.id = t2.in_id                                                     "
            +  "     	LEFT JOIN m_goods_spec t3 ON t1.sku_id = t3.id                                                  "
            +  "     WHERE TRUE                                                                                         "
            +  "     	AND t1.`status` = '"+DictConstant.DICT_B_IN_STATUS_TWO+"'                                    "
//            +  "     	AND t1.type = '"+DictConstant.DICT_B_IN_TYPE_JG_TH+"'                                           "
            +  "     	GROUP BY t1.warehouse_id,t3.goods_id                                                            "
            + "     ) tt10 ON tt10.warehouse_id = tt2.warehouse_id AND tt10.goods_id = tt2.goods_id                     "

            + "		left join m_goods tt6 on tt2.goods_id = tt6.id                                                      "
            + "		left join m_warehouse tt7 on tt2.warehouse_id = tt7.id                                              "
            + "     LEFT JOIN v_dict_info tt8 ON tt8.code = '" + DictConstant.DICT_M_WAREHOUSE_TYPE +"' and tt7.warehouse_type = tt8.dict_value"
            + "     where (tt7.warehouse_type = #{p1.warehouse_type}  or #{p1.warehouse_type,jdbcType=VARCHAR} is null or #{p1.warehouse_type,jdbcType=VARCHAR} = '')"
            + "<if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                                    "
            + "     and tt2.warehouse_id in                                                                             "
            + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>  "
            + "         #{item}                                                                                         "
            + "     </foreach>                                                                                          "
            + "</if>                                                                                                    "
            + " and (tt7.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '')                          "
            + "     and (tt7.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '' )                     "
            + "     and (tt6.name like concat('%', #{p1.goods_name}, '%') or  #{p1.goods_name} is null or  #{p1.goods_name} = '')"
            + "     and (tt6.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')  "
            + "  group by   tt7.warehouse_type, tt2.goods_id ) tab                                                      "
            + "     ,(select @row_num:=0) tab1                                                                          "
            + " where round(tab.in_qty, 4) != 0                                                                         "
            + " or round(tab.out_qty, 4) != 0                                                                           "
            + " or round(tab.qty_diff, 4) != 0                                                                          "
            + " or round(tab.qty_avaible, 4) != 0                                                                       "
            + "</script>                                                                                                "
    )
    List<BWarehouseGoodsExportVo> queryReportInventoryExportAll(@Param("p1") BWarehouseGoodsVo searchCondition);

    @Select({
            "<script>"
                    + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
                    + "		SELECT                                                                                     "
                    + "			SUM(qty_diff)  qty                                                                     "
                    + "		FROM                                                                                       "
                    + "			b_adjust_detail t                                                                      "
                    + "		LEFT JOIN m_warehouse t1 ON t.warehouse_id = t1.id                                         "
                    + "		LEFT JOIN m_goods_spec t2 ON t.sku_id = t2.id                                              "
                    + "		LEFT JOIN m_goods t3 on t2.goods_id = t3.id                                                "
                    + "	    WHERE t.`status` = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                      "

                    + "<if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                           "
                    + "     and t1.id in"
                    + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>"
                    + "         #{item}                                                                                "
                    + "     </foreach>                                                                                 "
                    + "</if>                                                                                           "
                    + " and (t1.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '')                  "
                    + " and (t3.name like concat('%', #{p1.goods_name}, '%') or #{p1.goods_name} is null or #{p1.goods_name} = '')"
                    + " and (t3.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')"
                    + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
                    + "${p1.params.dataScopeAnnotation}                                                                "
                    + "</script>                                                                                       "
    })
    BWarehouseGoodsVo queryAdjustInventorySum(@Param("p1") BWarehouseGoodsVo searchCondition);

    @Select({
            "<script>"
                    + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
                    + "		SELECT                                                                                     "
                    + "			t3.`name` goods_name,                                                                  "
                    + "			t3.`code` goods_code,                                                                  "
                    + "			t1.`name` warehouse_name,                                                              "
                    + "			SUM(qty_diff)  qty,                                                                  "
                    + "         t4.label as warehouse_type_name   ,                                                    "
                    + "         @row_num:= @row_num+ 1 as no                                                           "
                    + "		FROM                                                                                       "
                    + "			b_adjust_detail t                                                                      "
                    + "		LEFT JOIN m_warehouse t1 ON t.warehouse_id = t1.id                                         "
                    + "		LEFT JOIN m_goods_spec t2 ON t.sku_id = t2.id                                              "
                    + "		LEFT JOIN m_goods t3 on t2.goods_id = t3.id                                                "
                    + "     LEFT JOIN v_dict_info t4 ON t4.code = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE +"' and t1.warehouse_type = t4.dict_value"
                    + "     ,(select @row_num:=0) t6                                                                   "
                    + "	    WHERE t.`status` = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                      "

                    + "<if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                           "
                    + "     and t1.id in"
                    + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>"
                    + "         #{item}                                                                                "
                    + "     </foreach>                                                                                 "
                    + "</if>                                                                                           "
                    + " and (t1.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '')                  "
                    + " and (t3.name like concat('%', #{p1.goods_name}, '%') or #{p1.goods_name} is null or #{p1.goods_name} = '')"
                    + " and (t3.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')"
                    + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
                    + "${p1.params.dataScopeAnnotation}                                                                "
                    + "		GROUP BY t.warehouse_id, t3.id                                                             "
                    + "</script>                                                                                       "
    })
    List<BWarehouseGoodsAdjustExportVo> queryAdjustInventoryExportAll(@Param("p1") BWarehouseGoodsVo searchCondition);

    @Select({
            "<script>"
                    + "		SELECT                                                                                     "
                    + "			t3.`name` goods_name,                                                                  "
                    + "			t3.`code` goods_code,                                                                  "
                    + "			t1.`name` warehouse_name,                                                              "
                    + "			SUM(qty_diff)  qty,                                                                  "
                    + "         t4.label as warehouse_type_name   ,                                                    "
                    + "         @row_num:= @row_num+ 1 as no                                                           "
                    + "		FROM                                                                                       "
                    + "			b_adjust_detail t                                                                      "
                    + "		LEFT JOIN m_warehouse t1 ON t.warehouse_id = t1.id                                         "
                    + "		LEFT JOIN m_goods_spec t2 ON t.sku_id = t2.id                                              "
                    + "		LEFT JOIN m_goods t3 on t2.goods_id = t3.id                                                "
                    + "     LEFT JOIN v_dict_info t4 ON t4.code = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE +"' and t1.warehouse_type = t4.dict_value"
                    + "     ,(select @row_num:=0) t6                                                                   "
                    + "	    WHERE t.`status` = " + DictConstant.DICT_B_ADJUST_STATUS_PASSED
                    + "<if test='p1 != null and p1.size != 0'>                                                         "
                    + "    and concat(t1.id, '_', t3.id) in                                                            "
                    + "    <foreach collection='p1' item='item' index='index' open='(' separator=',' close=')'>        "
                    + "       #{item.id}                                                                               "
                    + "    </foreach>"
                    + "</if>                                                                                           "
                    + "GROUP BY t.warehouse_id, t3.id                                                                  "
                    + "</script>                                                                                       "
    })
    List<BWarehouseGoodsAdjustExportVo> queryAdjustInventoryExport(@Param("p1") List<BWarehouseGoodsVo> searchCondition);


    @Select({"<script>                                                                                                  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                   "
            + " SELECT                                                                                                  "
            + "   tab.goods_name,                                                                                       "
            + "   tab.goods_code,                                                                                       "
            + "   tab.warehouse_type_name,                                                                              "
            + "   tab.warehouse_name,                                                                                   "
            + "   tab.qty,                                                                                              "
            + "   @row_num:= @row_num+ 1 as no                                                                          "
            + "  FROM (                                                                                                 "
            +  "  SELECT                                                                                                "
            + "		t2.`name` goods_name,                                                                               "
            + "		t2.`code` goods_code,                                                                               "
            + "		t4.label as warehouse_type_name,                                                                    "
            + "     t3.name warehouse_name,                                                                             "
            +  "  	SUM( t.qty_avaible ) qty                                                                            "
            +  "  FROM                                                                                                  "
            +  "  	`m_inventory` t                                                                                     "
            +  "  	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                       "
            +  "	left join m_goods t2 on t1.goods_id = t2.id                                                         "
            +  "	left join m_warehouse t3 on t.warehouse_id = t3.id                                                  "
            +  "     LEFT JOIN v_dict_info t4 ON t4.code = '" + DictConstant.DICT_M_WAREHOUSE_TYPE +"' and t3.warehouse_type = t4.dict_value "
            + "     where (t3.warehouse_type = #{p1.warehouse_type}  or #{p1.warehouse_type,jdbcType=VARCHAR} is null or #{p1.warehouse_type,jdbcType=VARCHAR} = '')"
            + " <if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                                   "
            + "     and t.warehouse_id in"
            + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>  "
            + "         #{item}                                                                                         "
            + "     </foreach>                                                                                          "
            + "</if>                                                                                                    "
            + " and (t3.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '' )                          "
            + " and (t2.name like concat('%', #{p1.goods_name}, '%') or #{p1.goods_name} is null or #{p1.goods_name} = '')"
            + " and (t2.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')"
            + " ${p1.params.dataScopeAnnotation}                                                                   "
            +  "  GROUP BY                                                                                              "
            +  "  	t.warehouse_id,                                                                                     "
            +  "  	t1.goods_id                                                                                         "
            +  " ) tab                                                                                                  "
            +  "   ,(select @row_num:=0) tt9                                                                             "
            +  " </script>                                                                                              "
    })
    List<BWarehouseGoodsTotalExportVo> queryReportTotalExportAll(@Param("p1") BWarehouseGoodsVo searchCondition);

    @Select({"<script>                                                                                                  "
            +  "  SELECT                                                                                                "
            + "     @row_num:= @row_num+ 1 as no,                                                                       "
            + "		t2.`name` goods_name,                                                                               "
            + "		t2.`code` goods_code,                                                                               "
            + "		t4.label as warehouse_type_name,                                                                    "
            + "     t3.name warehouse_name,                                                                             "
            +  "  	SUM( t.qty_avaible ) qty                                                                            "
            +  "  FROM                                                                                                  "
            +  "  	`m_inventory` t                                                                                     "
            +  "  	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                       "
            +  "	left join m_goods t2 on t1.goods_id = t2.id                                                         "
            +  "	left join m_warehouse t3 on t.warehouse_id = t3.id                                                  "
            +  "     LEFT JOIN v_dict_info t4 ON t4.code = '" + DictConstant.DICT_M_WAREHOUSE_TYPE +"' and t3.warehouse_type = t4.dict_value"
            + "   ,(select @row_num:=0) tt9                                                                             "
            +  "<if test='p1 != null and p1.size != 0'>                                                                 "
            +  "    where concat(t.warehouse_id, '_', t1.goods_id) in                                                   "
            +  "    <foreach collection='p1' item='item' index='index' open='(' separator=',' close=')'>                "
            +  "       #{item.id}                                                                                       "
            +  "    </foreach>                                                                                          "
            +  "</if>                                                                                                   "
            +  "  GROUP BY                                                                                              "
            +  "  	t.warehouse_id,                                                                                     "
            +  "  	t1.goods_id                                                                                         "
            +  " </script>                                                                                              "
    })
    List<BWarehouseGoodsTotalExportVo> queryReportTotalExport(@Param("p1") List<BWarehouseGoodsVo> searchCondition);

    @Select({"<script>                                                                                                  "
            + "select                                                                                                   "
            + "   tab.no,                                                                                               "
            + "   tab.in_qty,                                                                                           "
            + "   tab.out_qty,                                                                                          "
            + "   tab.qty_diff,                                                                                         "
            + "   tab.qty_avaible qty,                                                                                  "
            + "   tab.goods_name,                                                                                       "
            + "   tab.goods_code,                                                                                       "
            + "   tab.warehouse_type_name,                                                                              "
            + "   tab.warehouse_name,                                                                                    "
            + "   tab.return_qty                                                                                    "
            + " from (                                                                                                  "
            + "	SELECT                                                                                                  "
            + "     @row_num:= @row_num+ 1 as no,                                                                       "
            + "		ifnull(sum(tt3.in_qty), 0) in_qty,                                                                  "
            + "		ifnull(sum(tt4.out_qty), 0) out_qty,                                                                "
            + "		ifnull(sum(tt5.qty_diff), 0) qty_diff,                                                              "
            + "		ifnull(sum(tt9.qty_avaible), 0) qty_avaible,                                                        "
            + "		tt6.`name` goods_name,                                                                              "
            + "		tt6.`code` goods_code,                                                                              "
            + "		tt8.label as warehouse_type_name,                                                                   "
            + "     tt7.name warehouse_name,                                                                             "
            + "     ifnull(sum(tt10.return_qty), 0) return_qty                                                          "
            + "	FROM                                                                                                    "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		FROM                                                                                                "
            + "			(                                                                                               "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_in t                                                                                      "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_out t                                                                                     "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_adjust_detail t                                                                           "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + "         UNION ALL                                                                                       "
            + "      SELECT t.warehouse_id, t1.goods_id FROM m_inventory t LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id"
            + "			) tt1                                                                                           "
            + "		GROUP BY                                                                                            "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		) tt2                                                                                               "
            + "		LEFT JOIN (                                                                                         "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight ) in_qty                                                                     "
            + "		FROM                                                                                                "
            + "			b_in t                                                                                          "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt3 ON tt2.warehouse_id = tt3.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt3.goods_id                                                                     "
            + "		left join                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight ) - sum(ifnull(return_qty,0)) out_qty                                        "
            + "		FROM                                                                                                "
            + "			b_out t                                                                                         "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt4 on tt2.warehouse_id = tt4.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt4.goods_id                                                                     "
            + "		left JOIN                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( qty_diff ) qty_diff                                                                        "
            + "		FROM                                                                                                "
            + "			b_adjust_detail t                                                                               "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt5 on tt2.warehouse_id = tt5.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt5.goods_id                                                                     "
            + "     LEFT JOIN (                                                                                         "
            +  "      SELECT                                                                                            "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id,                                                                                    "
            +  "      	SUM( t.qty_avaible ) qty_avaible                                                                "
            +  "      FROM                                                                                              "
            +  "      	`m_inventory` t                                                                                 "
            +  "      	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "      GROUP BY                                                                                          "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id                                                                                     "
            + "     ) tt9 ON tt9.warehouse_id = tt2.warehouse_id AND tt9.goods_id = tt2.goods_id                        "

            + "     LEFT JOIN (                                                                                         "
            +  "     SELECT                                                                                             "
            +  "     	sum( t1.actual_count ) AS return_qty,                                                         "
            +  "     	t1.warehouse_id,                                                                                "
            +  "     	t3.goods_id                                                                                     "
            +  "     FROM                                                                                               "
            +  "     	b_in t1                                                                                         "
            +  "     	LEFT JOIN b_in_extra t2 ON t1.id = t2.in_id                                                     "
            +  "     	LEFT JOIN m_goods_spec t3 ON t1.sku_id = t3.id                                                  "
            +  "     WHERE TRUE                                                                                         "
            +  "     	AND t1.`status` = '"+DictConstant.DICT_B_IN_STATUS_TWO+"'                                     "
//            +  "     	AND t1.type = '"+DictConstant.DICT_B_IN_TYPE_JG_TH+"'                                            "
            +  "     	GROUP BY t1.warehouse_id,t3.goods_id                                            "
            + "     ) tt10 ON tt10.warehouse_id = tt2.warehouse_id AND tt10.goods_id = tt2.goods_id                      "

            + "		left join m_goods tt6 on tt2.goods_id = tt6.id                                                      "
            + "		left join m_warehouse tt7 on tt2.warehouse_id = tt7.id                                              "
            + "     LEFT JOIN v_dict_info tt8 ON tt8.code = '" + DictConstant.DICT_M_WAREHOUSE_TYPE +"' and tt7.warehouse_type = tt8.dict_value"
            + "   ,(select @row_num:=0) tt11                                                                             "
            + "<if test='p1 != null and p1.size != 0'>                                                                  "
            + "    where concat(tt2.warehouse_id, '_', tt2.goods_id) in                                                 "
            + "    <foreach collection='p1' item='item' index='index' open='(' separator=',' close=')'>                 "
            + "       #{item.id}                                                                                        "
            + "    </foreach>                                                                                           "
            + "</if>                                                                                                    "
            + "  group by   tt2.warehouse_id, tt2.goods_id) tab                                                         "
            + "</script>                                                                                                "
    })
    List<BWarehouseInventoryExportVo> queryReportExport(@Param("p1") List<BWarehouseGoodsVo> searchCondition);

    @Select("<script>                                                                                                   "
            + " ${p1.params.dataScopeAnnotation_with}                                                                   "
            + " select                                                                                                  "
            + "   tab.in_qty,                                                                                           "
            + "   tab.out_qty,                                                                                          "
            + "   tab.qty_diff,                                                                                         "
            + "   tab.goods_name,                                                                                       "
            + "   tab.goods_code,                                                                                       "
            + "   tab.warehouse_name,                                                                                   "
            + "   tab.warehouse_type_name,                                                                              "
            + "   tab.qty_avaible qty,                                                                                  "
            + "   tab.return_qty return_qty,                                                                                  "
            + "  @row_num:= @row_num+ 1 as no              from (                                                       "
            + "	SELECT                                                                                                  "
            + "		ifnull(sum(tt3.in_qty), 0) in_qty,                                                                  "
            + "		ifnull(sum(tt4.out_qty), 0) out_qty,                                                                "
            + "		ifnull(sum(tt5.qty_diff), 0) qty_diff,                                                              "
            + "		ifnull(sum(tt9.qty_avaible), 0) qty_avaible,                                                        "
            + "		tt6.`name` goods_name,                                                                              "
            + "		tt6.`code` goods_code,                                                                              "
            + "		tt7.`name` warehouse_name,                                                                          "
            + "     tt8.label as warehouse_type_name,                                                                   "
            + "     ifnull(sum(tt10.return_qty), 0) return_qty                                                          "
            + "	FROM                                                                                                    "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		FROM                                                                                                "
            + "			(                                                                                               "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_in t                                                                                      "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')   "
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_out t                                                                                     "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')   "
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_adjust_detail t                                                                           "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')   "
            + "         UNION ALL                                                                                       "
            + "      SELECT t.warehouse_id, t1.goods_id FROM m_inventory t LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id"
            + "			) tt1                                                                                           "
            + "     WHERE true                                                                                          "
            + "${p1.params.dataScopeAnnotation}                                                                         "
            + "		GROUP BY                                                                                            "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		) tt2                                                                                               "
            + "		LEFT JOIN (                                                                                         "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight ) in_qty                                                                     "
            + "		FROM                                                                                                "
            + "			b_in t                                                                                          "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')   "
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt3 ON tt2.warehouse_id = tt3.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt3.goods_id                                                                     "
            + "		left join                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight ) - sum(ifnull(return_qty,0)) out_qty                                        "
            + "		FROM                                                                                                "
            + "			b_out t                                                                                         "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED +"'                                       "
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')   "
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt4 on tt2.warehouse_id = tt4.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt4.goods_id                                                                     "
            + "		left JOIN                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( qty_diff ) qty_diff                                                                        "
            + "		FROM                                                                                                "
            + "			b_adjust_detail t                                                                               "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')   "
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt5 on tt2.warehouse_id = tt5.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt5.goods_id                                                                     "
            + "     LEFT JOIN (                                                                                         "
            +  "      SELECT                                                                                            "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id,                                                                                    "
            +  "      	SUM( t.qty_avaible ) qty_avaible                                                                "
            +  "      FROM                                                                                              "
            +  "      	`m_inventory` t                                                                                 "
            +  "      	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "      GROUP BY                                                                                          "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id                                                                                     "
            + "     ) tt9 ON tt9.warehouse_id = tt2.warehouse_id AND tt9.goods_id = tt2.goods_id                        "

             + "     LEFT JOIN (                                                                                         "
            +  "     SELECT                                                                                             "
            +  "     	sum( t1.actual_count ) AS return_qty,                                                         "
            +  "     	t1.warehouse_id,                                                                                "
            +  "     	t3.goods_id                                                                                     "
            +  "     FROM                                                                                               "
            +  "     	b_in t1                                                                                         "
            +  "     	LEFT JOIN b_in_extra t2 ON t1.id = t2.in_id                                                     "
            +  "     	LEFT JOIN m_goods_spec t3 ON t1.sku_id = t3.id                                                  "
            +  "     WHERE TRUE                                                                                         "
            +  "     	AND t1.`status` = '"+DictConstant.DICT_B_IN_STATUS_TWO+"'                                     "
//            +  "     	AND t1.type = '"+DictConstant.DICT_B_IN_TYPE_JG_TH+"'                                            "
            +  "     	GROUP BY t1.warehouse_id,t3.goods_id                                            "
            + "     ) tt10 ON tt10.warehouse_id = tt2.warehouse_id AND tt10.goods_id = tt2.goods_id                      "

            + "		left join m_goods tt6 on tt2.goods_id = tt6.id                                                      "
            + "		left join m_warehouse tt7 on tt2.warehouse_id = tt7.id                                              "
            + "     LEFT JOIN v_dict_info tt8 ON tt8.code = '" + DictConstant.DICT_M_WAREHOUSE_TYPE +"' and tt7.warehouse_type = tt8.dict_value"
            + "     where (tt7.warehouse_type = #{p1.warehouse_type}  or #{p1.warehouse_type,jdbcType=VARCHAR} is null or #{p1.warehouse_type,jdbcType=VARCHAR} = '')"
            + "<if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                                    "
            + "     and tt2.warehouse_id in                                                                             "
            + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>  "
            + "         #{item}                                                                                         "
            + "     </foreach>                                                                                          "
            + "</if>                                                                                                    "
            + " and (tt7.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '')                          "
            + " and (tt6.name like concat('%', #{p1.goods_name}, '%') or #{p1.goods_name} is null or  #{p1.goods_name} = '')"
            + " and (tt6.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')"
            + " group by   tt2.warehouse_id, tt2.goods_id                                                               "
            + "    ) tab                                                                                                "
            + "   ,(select @row_num:=0) tt9                                                                             "
            + " where round(tab.in_qty, 4) != 0                                                                         "
            + " or round(tab.out_qty, 4) != 0                                                                           "
            + " or round(tab.qty_diff, 4) != 0                                                                          "
            + " or round(tab.qty_avaible, 4) != 0                                                                       "
            + "</script>                                                                                                "
    )
    List<BWarehouseInventoryExportVo> queryReportExportAll(@Param("p1") BWarehouseGoodsVo searchCondition);

    @Select({" ${p1.params.dataScopeAnnotation_with}                                                                    "
            + "	SELECT                                                                                                   "
            + "		tt2.warehouse_id ,                                                                                   "
            + "		tt2.goods_id ,                                                                                       "
            + "     tt6.code goods_code,                                                                                 "
            + "		ifnull(sum(tt3.in_qty), 0) in_qty,                                                                   "
            + "		ifnull(sum(tt4.out_qty), 0) out_qty,                                                                 "
            + "		ifnull(sum(tt5.qty_diff), 0) qty_diff,                                                               "
            + "		ifnull(sum(tt9.qty_avaible), 0) qty,                                                                 "
            + "		tt6.`name` goods_name,                                                                               "
            + "		tt7.`name` warehouse_name,                                                                           "
            + "     tt8.label as warehouse_type_name,                                                                    "
            + "     tt7.warehouse_type,                                                                                  "
            + "     concat(tt7.warehouse_type, '_', tt2.goods_id)  type_id,                                              "
            + "     concat(tt7.id, '_', tt2.goods_id)  id                                                                "
            + "	FROM                                                                                                     "
            + "		(                                                                                                    "
            + "		SELECT                                                                                               "
            + "			tt1.warehouse_id,                                                                                "
            + "			tt1.goods_id                                                                                     "
            + "		FROM                                                                                                 "
            + "			(                                                                                                "
            + "			SELECT                                                                                           "
            + "				t.warehouse_id,                                                                              "
            + "				t1.goods_id                                                                                  "
            + "			FROM                                                                                             "
            + "				b_in t                                                                                       "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                                "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                        "
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) >= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')   "
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_out t                                                                                     "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) >= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')  "
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_adjust_detail t                                                                           "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) >= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')  "
            + "         UNION ALL                                                                                       "
            + "      SELECT t.warehouse_id, t1.goods_id FROM m_inventory t LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id"
            + "			) tt1                                                                                           "
            + "     WHERE true                                                                                          "
            + " ${p1.params.dataScopeAnnotation}                                                                        "
            + "		GROUP BY                                                                                            "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		) tt2                                                                                               "
            + "		LEFT JOIN (                                                                                         "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_count ) in_qty                                                                      "
            + "		FROM                                                                                                "
            + "			b_in t                                                                                          "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "			LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                              "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
            // 排除仓库类型是2时的生产入库类型的入库单
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) >= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')  "
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt3 ON tt2.warehouse_id = tt3.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt3.goods_id                                                                     "
            + "		left join                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight ) - sum(ifnull(return_qty,0)) out_qty                                                                    "
//                   + "		sum(IF(t2.warehouse_type = '2', IF(t.type = '7', 0, actual_weight),actual_weight)) out_qty "
            + "		FROM                                                                                                "
            + "			b_out t                                                                                         "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "         LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                              "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) >= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')  "
//                          排除当 仓库类型是2时的领料出库类型的出库单
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt4 on tt2.warehouse_id = tt4.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt4.goods_id                                                                     "
            + "		left JOIN                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( qty_diff ) qty_diff                                                                        "
            + "		FROM                                                                                                "
            + "			b_adjust_detail t                                                                               "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) >= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')  "
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt5 on tt2.warehouse_id = tt5.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt5.goods_id                                                                     "
            + "     LEFT JOIN (                                                                                         "
            +  "      SELECT                                                                                            "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id,                                                                                    "
            +  "      	SUM( t.qty_avaible ) qty_avaible                                                                "
            +  "      FROM                                                                                              "
            +  "      	`m_inventory` t                                                                                 "
            +  "      	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "      GROUP BY                                                                                          "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id                                                                                     "
            + "     ) tt9 ON tt9.warehouse_id = tt2.warehouse_id AND tt9.goods_id = tt2.goods_id                        "
            + "		left join m_goods tt6 on tt2.goods_id = tt6.id                                                      "
            + "		left join m_warehouse tt7 on tt2.warehouse_id = tt7.id                                              "
            + "     LEFT JOIN v_dict_info tt8 ON tt8.code = '" + DictConstant.DICT_M_WAREHOUSE_TYPE +"' and tt7.warehouse_type = tt8.dict_value"
            + "     where tt7.warehouse_type != '" + DictConstant.DICT_M_WAREHOUSE_TYPE_CL + "'                         "
            + "     group by   tt7.warehouse_type                                                                       "
    })
    List<BWarehouseGoodsVo> queryWarehouseTypeList(@Param("p1") BWarehouseGoodsVo searchCondition);

    /**
     * 按仓库类型商品 导出数量查询
     * @param searchCondition
     * @return
     */
    @Select("<script>                                                                                                   "
            + " ${p1.params.dataScopeAnnotation_with}                                                                   "
            + " select count(1) as qty from (                                                                           "
            + "	SELECT                                                                                                  "
            + "		ifnull(sum(tt3.in_qty), 0) in_qty,                                                                  "
            + "		ifnull(sum(tt4.out_qty), 0) out_qty,                                                                "
            + "		ifnull(sum(tt5.qty_diff), 0) qty_diff,                                                              "
            + "		ifnull(sum(tt9.qty_avaible), 0) qty,                                                                 "
            + "		ifnull(sum(tt10.return_qty), 0) return_qty                                                         "
            + "	FROM                                                                                                    "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		FROM                                                                                                "
            + "			(                                                                                               "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_in t                                                                                      "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
            // 排除仓库类型是2时的生产入库类型的入库单
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "         UNION ALL                                                                                        "
            + "			SELECT                                                                                           "
            + "				t.warehouse_id,                                                                              "
            + "				t1.goods_id                                                                                  "
            + "			FROM                                                                                             "
            + "				b_out t                                                                                      "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                           "
            + "		WHERE                                                                                                "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                       "
            // 排除当 仓库类型是2时的领料出库类型的出库单
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_adjust_detail t                                                                           "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "         UNION ALL                                                                                       "
            + "      SELECT t.warehouse_id, t1.goods_id FROM m_inventory t LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id"
            + "			) tt1                                                                                           "
            + "     WHERE true                                                                                          "
            + " ${p1.params.dataScopeAnnotation}                                                                        "
            + "		GROUP BY                                                                                            "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		) tt2                                                                                               "
            + "		LEFT JOIN (                                                                                         "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight ) in_qty                                                                     "
            + "		FROM                                                                                                "
            + "			b_in t                                                                                          "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
            // 排除仓库类型是2时的生产入库类型的入库单
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt3 ON tt2.warehouse_id = tt3.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt3.goods_id                                                                     "
            + "		left join                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight )  - sum(ifnull(return_qty,0)) out_qty                                                  "
            + "		FROM                                                                                                "
            + "			b_out t                                                                                         "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
            // 排除当 仓库类型是2时的领料出库类型的出库单
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt4 on tt2.warehouse_id = tt4.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt4.goods_id                                                                     "
            + "		left JOIN                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( qty_diff ) qty_diff                                                                        "
            + "		FROM                                                                                                "
            + "			b_adjust_detail t                                                                               "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + "     and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt5 on tt2.warehouse_id = tt5.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt5.goods_id                                                                     "
            + "     LEFT JOIN (                                                                                         "
            +  "      SELECT                                                                                            "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id,                                                                                    "
            +  "      	SUM( t.qty_avaible ) qty_avaible                                                                "
            +  "      FROM                                                                                              "
            +  "      	`m_inventory` t                                                                                 "
            +  "      	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "      GROUP BY                                                                                          "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id                                                                                     "
            + "     ) tt9 ON tt9.warehouse_id = tt2.warehouse_id AND tt9.goods_id = tt2.goods_id                        "

            + "     LEFT JOIN (                                                                                         "
            +  "     SELECT                                                                                             "
            +  "     	sum( t1.actual_count ) AS return_qty,                                                           "
            +  "     	t1.warehouse_id,                                                                                "
            +  "     	t3.goods_id                                                                                     "
            +  "     FROM                                                                                               "
            +  "     	b_in t1                                                                                         "
            +  "     	LEFT JOIN b_in_extra t2 ON t1.id = t2.in_id                                                     "
            +  "     	LEFT JOIN m_goods_spec t3 ON t1.sku_id = t3.id                                                  "
            +  "     WHERE TRUE                                                                                         "
            +  "     	AND t1.`status` = '"+DictConstant.DICT_B_IN_STATUS_TWO+"'                                    "
//            +  "     	AND t1.type = '"+DictConstant.DICT_B_IN_TYPE_JG_TH+"'                                           "
            +  "     	GROUP BY t1.warehouse_id,t3.goods_id                                                            "
            + "     ) tt10 ON tt10.warehouse_id = tt2.warehouse_id AND tt10.goods_id = tt2.goods_id                     "

            + "		left join m_goods tt6 on tt2.goods_id = tt6.id                                                      "
            + "		left join m_warehouse tt7 on tt2.warehouse_id = tt7.id                                              "
            + "     where (tt7.warehouse_type = #{p1.warehouse_type}  or #{p1.warehouse_type,jdbcType=VARCHAR} is null or #{p1.warehouse_type,jdbcType=VARCHAR} = '')"
            + "     and (tt7.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '' )                     "
            + "     and (tt6.name like concat('%', #{p1.goods_name}, '%') or  #{p1.goods_name} is null or  #{p1.goods_name} = '')"
            + "     and (tt6.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')  "
            + "<if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                                    "
            + "     and tt2.warehouse_id in                                                                             "
            + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>  "
            + "         #{item}                                                                                         "
            + "     </foreach>                                                                                          "
            + "</if>                                                                                                    "
            + "  group by   tt7.warehouse_type, tt2.goods_id                                                            "
            + "  ) tab                                                                                                  "
            + " where round(tab.in_qty, 4) != 0                                                                         "
            + " or round(tab.out_qty, 4) != 0                                                                           "
            + " or round(tab.qty_diff, 4) != 0                                                                          "
            + " or round(tab.qty, 4) != 0                                                                               "
            + "</script>                                                                                                "
    )
    int selectExportNum(@Param("p1") BWarehouseGoodsVo searchCondition);

    /**
     * 按仓库类型商品 - 调整 导出数量查询
     * @param searchCondition
     * @return
     */
    @Select({
            "<script>"
                    + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
                    + "  select count(1) from (                                                                        "
                    + "		SELECT                                                                                     "
                    + "			t.id                                                                                   "
                    + "		FROM                                                                                       "
                    + "			b_adjust_detail t                                                                      "
                    + "		LEFT JOIN m_warehouse t1 ON t.warehouse_id = t1.id                                         "
                    + "		LEFT JOIN m_goods_spec t2 ON t.sku_id = t2.id                                              "
                    + "		LEFT JOIN m_goods t3 on t2.goods_id = t3.id                                                "
                    + "	    WHERE t.`status` = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                      "
                    + "<if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                           "
                    + "     and t1.id in"
                    + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>"
                    + "         #{item}                                                                                "
                    + "     </foreach>                                                                                 "
                    + "</if>                                                                                           "
                    + " and (t1.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '')                  "
                    + " and (t3.name like concat('%', #{p1.goods_name}, '%') or #{p1.goods_name} is null or #{p1.goods_name} = '')"
                    + " and (t3.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')"
                    + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')"
                    + "${p1.params.dataScopeAnnotation}                                                                "
                    + "		GROUP BY t.warehouse_id, t3.id ) tt1                                                            "
                    + "</script>                                                                                       "
    })
    int selectAdjustExportNum(@Param("p1")  BWarehouseGoodsVo searchCondition);

    /**
     * 按仓库类型商品 - 合计 导出数量查询
     * @param searchCondition
     * @return
     */
    @Select("<script>                                                                                                   "
            + " ${p1.params.dataScopeAnnotation_with}                                                                   "
            + " select count(1) as qty from (                                                                           "
            + "	SELECT                                                                                                  "
            + "		ifnull(sum(tt3.in_qty), 0) in_qty,                                                                  "
            + "		ifnull(sum(tt4.out_qty), 0) out_qty,                                                                "
            + "		ifnull(sum(tt5.qty_diff), 0) qty_diff,                                                              "
            + "		ifnull(sum(tt9.qty_avaible), 0) qty_avaible                                                         "
            + "	FROM                                                                                                    "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		FROM                                                                                                "
            + "			(                                                                                               "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_in t                                                                                      "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')   "
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_out t                                                                                     "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')   "
            + "         UNION ALL                                                                                       "
            + "			SELECT                                                                                          "
            + "				t.warehouse_id,                                                                             "
            + "				t1.goods_id                                                                                 "
            + "			FROM                                                                                            "
            + "				b_adjust_detail t                                                                           "
            + "				LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                               "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')   "
            + "         UNION ALL                                                                                       "
            + "      SELECT t.warehouse_id, t1.goods_id FROM m_inventory t LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id"
            + "			) tt1                                                                                           "
            + "     WHERE true                                                                                          "
            + " ${p1.params.dataScopeAnnotation}                                                                        "
            + "		GROUP BY                                                                                            "
            + "			tt1.warehouse_id,                                                                               "
            + "			tt1.goods_id                                                                                    "
            + "		) tt2                                                                                               "
            + "		LEFT JOIN (                                                                                         "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight ) in_qty                                                                     "
            + "		FROM                                                                                                "
            + "			b_in t                                                                                          "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_IN_STATUS_TWO + "'                                       "
//            + "     AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_IN_TYPE_SC + "')"
            + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')   "
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt3 ON tt2.warehouse_id = tt3.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt3.goods_id                                                                     "
            + "		left join                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( actual_weight ) out_qty                                                                    "
            + "		FROM                                                                                                "
            + "			b_out t                                                                                         "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "				LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                          "
            + "		WHERE                                                                                               "
//            + "			t.STATUS = '" + DictConstant.DICT_B_OUT_STATUS_PASSED + "'                                      "
//            + " AND NOT (t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"' AND t.type = '" + DictConstant.DICT_B_OUT_TYPE_LL + "')"
            + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')   "
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt4 on tt2.warehouse_id = tt4.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt4.goods_id                                                                     "
            + "		left JOIN                                                                                           "
            + "		(                                                                                                   "
            + "		SELECT                                                                                              "
            + "			t.warehouse_id,                                                                                 "
            + "			t1.goods_id,                                                                                    "
            + "			sum( qty_diff ) qty_diff                                                                        "
            + "		FROM                                                                                                "
            + "			b_adjust_detail t                                                                               "
            + "			LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            + "		WHERE                                                                                               "
            + "			t.STATUS = '" + DictConstant.DICT_B_ADJUST_STATUS_PASSED + "'                                   "
            + " and (DATE_FORMAT(t.c_time, '%Y-%m-%d' ) &gt;= #{p1.batch} or #{p1.batch} is null or #{p1.batch} = '')   "
            + "		GROUP BY t.warehouse_id, t1.goods_id                                                                "
            + "		) tt5 on tt2.warehouse_id = tt5.warehouse_id                                                        "
            + "		AND tt2.goods_id = tt5.goods_id                                                                     "
            + "     LEFT JOIN (                                                                                         "
            +  "      SELECT                                                                                            "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id,                                                                                    "
            +  "      	SUM( t.qty_avaible ) qty_avaible                                                                "
            +  "      FROM                                                                                              "
            +  "      	`m_inventory` t                                                                                 "
            +  "      	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "      GROUP BY                                                                                          "
            +  "      	t.warehouse_id,                                                                                 "
            +  "      	t1.goods_id                                                                                     "
            + "     ) tt9 ON tt9.warehouse_id = tt2.warehouse_id AND tt9.goods_id = tt2.goods_id                        "
            + "		left join m_goods tt6 on tt2.goods_id = tt6.id                                                      "
            + "		left join m_warehouse tt7 on tt2.warehouse_id = tt7.id                                              "
            + "     where (tt7.warehouse_type = #{p1.warehouse_type}  or #{p1.warehouse_type,jdbcType=VARCHAR} is null or #{p1.warehouse_type,jdbcType=VARCHAR} = '')"
            + "<if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                                    "
            + "     and tt2.warehouse_id in                                                                             "
            + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>  "
            + "         #{item}                                                                                         "
            + "     </foreach>                                                                                          "
            + "</if>                                                                                                    "
            + " and (tt7.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '' )                         "
            + " and (tt6.name like concat('%', #{p1.goods_name}, '%') or #{p1.goods_name} is null or #{p1.goods_name} = '')"
            + " and (tt6.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')"
            + " group by   tt2.warehouse_id, tt2.goods_id                                                               "
            + "    ) tab                                                                                                "
            + " where round(tab.in_qty, 4) != 0                                                                         "
            + " or round(tab.out_qty, 4) != 0                                                                           "
            + " or round(tab.qty_diff, 4) != 0                                                                          "
            + " or round(tab.qty_avaible, 4) != 0                                                                       "
            + "</script>                                                                                                "
    )
    int selectTotalExportNum(@Param("p1") BWarehouseGoodsVo searchCondition);

    @Select({"<script>                                                                                                  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                   "
            + "  SELECT                                                                                                 "
            + "		t2.`name` goods_name,                                                                               "
            + "		t2.`code` goods_code,                                                                               "
            + "		t4.label as warehouse_type_name,                                                                    "
            + "     t3.name warehouse_name,                                                                             "
            + "     concat(t.warehouse_id, '_', t1.goods_id) id,                                                        "
            + "  	SUM( t.qty_avaible ) qty                                                                            "
            + "  FROM                                                                                                   "
            + "  	`m_inventory` t                                                                                     "
            + "  	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                       "
            + "	left join m_goods t2 on t1.goods_id = t2.id                                                             "
            + "	left join m_warehouse t3 on t.warehouse_id = t3.id                                                      "
            + " LEFT JOIN v_dict_info t4 ON t4.code = '" + DictConstant.DICT_M_WAREHOUSE_TYPE + "' and t3.warehouse_type = t4.dict_value"
            + " where (t3.warehouse_type = #{p1.warehouse_type}  or #{p1.warehouse_type,jdbcType=VARCHAR} is null or #{p1.warehouse_type,jdbcType=VARCHAR} = '')"
            + "<if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                                    "
            + "     and t.warehouse_id in                                                                             "
            + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>  "
            + "         #{item}                                                                                         "
            + "     </foreach>                                                                                          "
            + "</if>                                                                                                    "
            + "     and (t3.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '')                      "
            + "     and (t2.name like concat('%', #{p1.goods_name}, '%') or #{p1.goods_name} is null or #{p1.goods_name} = '')"
            + "     and (t2.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')"
            + "${p1.params.dataScopeAnnotation}                                                                "
            + "  GROUP BY                                                                                              "
            + "  	t.warehouse_id,                                                                                     "
            + "  	t1.goods_id                                                                                         "
            + " </script>                                                                                              "
    })
    IPage<BWarehouseGoodsVo> selectTotalPageList(Page<BAdjustDetailEntity> pageCondition, @Param("p1") BWarehouseGoodsVo searchCondition);

    @Select({"<script>                                                                                                  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                   "
            + "  SELECT                                                                                                 "
            + "  	SUM( t.qty_avaible ) qty                                                                            "
            + "  FROM                                                                                                   "
            + "  	`m_inventory` t                                                                                     "
            + "  	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                       "
            + "	left join m_goods t2 on t1.goods_id = t2.id                                                             "
            + "	left join m_warehouse t3 on t.warehouse_id = t3.id                                                      "
            + " where (t3.warehouse_type = #{p1.warehouse_type}  or #{p1.warehouse_type,jdbcType=VARCHAR} is null or #{p1.warehouse_type,jdbcType=VARCHAR} = '')"
            + "<if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                                    "
            + "     and t.warehouse_id in                                                                               "
            + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>  "
            + "         #{item}                                                                                         "
            + "     </foreach>                                                                                          "
            + "</if>                                                                                                    "
            + "     and (t3.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '')                       "
            + "     and (t2.name like concat('%', #{p1.goods_name}, '%') or #{p1.goods_name} is null or #{p1.goods_name} = '')"
            + "     and (t2.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')"
            + "${p1.params.dataScopeAnnotation}                                                                         "
            + " </script>                                                                                               "
    })
    BWarehouseGoodsVo selectTotalPageListSum(@Param("p1") BWarehouseGoodsVo searchCondition);

    @Select({"<script>                                                                                                  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                   "
            + "  SELECT                                                                                                 "
            + "  	count(1)                                                                                            "
            + "  FROM                                                                                                   "
            + "  	`m_inventory` t                                                                                     "
            + "  	LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                       "
            + "	left join m_goods t2 on t1.goods_id = t2.id                                                             "
            + "	left join m_warehouse t3 on t.warehouse_id = t3.id                                                      "
            + " where (t3.warehouse_type = #{p1.warehouse_type}  or #{p1.warehouse_type,jdbcType=VARCHAR} is null or #{p1.warehouse_type,jdbcType=VARCHAR} = '')"
            + "<if test='p1.warehouse_ids != null and p1.warehouse_ids.length != 0'>                                    "
            + "     and t.warehouse_id in                                                                               "
            + "     <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>  "
            + "         #{item}                                                                                         "
            + "     </foreach>                                                                                          "
            + "</if>                                                                                                    "
            + "     and (t3.warehouse_type = #{p1.type} or #{p1.type} is null or #{p1.type} = '')                       "
            + "     and (t2.name like concat('%', #{p1.goods_name}, '%') or #{p1.goods_name} is null or #{p1.goods_name} = '')"
            + "     and (t2.code like concat('%', #{p1.goods_code}, '%') or #{p1.goods_code} is null or #{p1.goods_code} = '')"
            + "${p1.params.dataScopeAnnotation}                                                                         "
            + " </script>                                                                                               "
    })
    int selectTotalCount(@Param("p1") BWarehouseGoodsVo searchCondition);
}
